SCADA Signals - Database Source
When the user selects a database source, the user is indicating that the SCADA data (whether it is from a SCADA system, logger or some other source) will be in the form of a file or database as opposed to retrieving data directly from a SCADA OPC server. Datasources can be managed with ProjectWise. The file types and format are described below.
As of SELECTseries 5, WaterGEMS/CAD currently supports the following although more could be added In the future:
- Excel 3.0
- Excel 4.0
- Excel 5.0
- Excel 2003/XP/2000/97 (8.0)
- Excel 2007 (12.0)
- Access 2.0
- Access 97/7.0 (3.0)
- Access 2003/2002/2000 (4.0)
- Access 2007 (12.0)
- OBDC Source
- OLEDB Source
- SQL Source
- Oracle connection
There are essentially two formats for the signals to be presented to the model: One value per row or multiple values per row.
Multiple values per row. In this format, usually found when SCADA data have been processed, there can be multiple values for each record or row corresponding to a single date-time. The signal name needs to be the first row of the table. The signal names will usually correspond to the tags from the SCADA system. Not all of the columns/fields in the data source need to be used. An example of such data is shown below.
| Time Stamp | Pump B Flow | Level South Tank | West Pressure Gauge | Flow Plant TM |
|---|---|---|---|---|
| 3:45:14 15 June, 2016 | 375.788 | 34.44 | 87.5 | 12.356 |
| 3:45:15 15 June, 2016 | 376.114 | 34.41 | 87.1 | 12.319 |
| 3:45:16 15 June, 2016 | 0.015 | 34.38 | 85.6 | 12.189 |
Once the user has selected a Database source and chooses to Edit the following dialog is displayed where the connection can be configured and signals from the data source selected.
The fields are described below:
Connection actually establishes the connection with the data file. Picking Edit will open the dialog below:
The Data Source Type field will open a drop-down list of the available types as listed earlier. In case of Excel and Access the Data Source is the actual file with a full path that is selected by clicking the ellipse button. Once the path is provided, it is suggested to pick Test Connection to ensure that the source is set up correctly. For Data Source Type ODBC, OLEDB, SQL and Oracle connections, additional information, which includes such items as login information, is required in a dialog as shown below:
The Advanced button opens a dialog that allows to adopt delimiters used in SQL statements. For the well-defined data source types (Excel, Access, Oracle) the values are preconfigured. Generic data source types may need modifications:
The Connection String is automatically established by the program. Advanced users can edit this string for Generic Data Source Types.
The Table Name field opens a drop down which enables the user to pick the table from the table corresponding to this datasource. If multiple datasources are used, each must have its own connection.
The Source Format identifies which of the formats: one value per row or multiple values per row is to be used.
The Signal Value Field identifies which table column is to be used as the signal name when the one value per row format is used. It is not used for multiple values per row.
The Value Field identifies which table column is to be used as the Value when the one value per row format is used. It is not used for multiple values per row.
The Time Stamp Field is used to identify which table column contains the time stamp. Any Windows-compliant Date/Time format can be used.
The Questionable Field identifies which table column is to be used as the data quality field when the one value per row format is used. It is not used for multiple values per row. For data to be considered acceptable for use, this field must contain the word "Good" although it may be part of a longer string (e.g. "Good data").
The Options portion of the dialog identifies which type of data this is to be considered. If the Real Time button is picked, then only the most recent value is imported while if Historical is picked, all values in the time band are used except for cases when a single value is needed such as Initial Conditions or Darwin Calibrator, in which case the Time Tolerance is used to pick the correct value from the historical datasource.
Once the user has identified the Data Source, the user can pick the Select SCADA Signals button which opens the dialog below which enables the user to select the signals that will be available in the model. These should correspond to the properties that are available for model elements plus any user defined properties. This is done by highlighting the signals in the left pane and picking Add to move them to the right pane.
These signals are added to the list of available signals by highlighting them in the left pane and picking the Add button to move them to the right pane.
Clicking OK on the Database Source dialog performs a number of validations including verifying that:
- Datasource is available
- The selected table is valid
- Signal names are valid
- Questionable field exists (if it was selected)
- Time/date field is available (if Historical is selected)
The second tab on the Datasource editor dialog is the Units tab which enables the user to specify the units for the SCADA signals. The default values are the values specified for the parameters in the model. However, if the units in the SCADA system datasource are different, this is where the user can indicate what those units are so that they can be adjusted when being imported.
The user picks the field in the right column and then selects the correct units from the drop down list.
In some cases, the values from the database source must be transformed into values that are expected in the model. Use SCADA Signal Mapping tab to set up these transformations (see SCADA Signal Mapping).
SCADA Signals - Units
The second tab on the Datasource editor dialog is the Units tab which enables the user to specify the units for the SCADA signals. The default values are the values specified for the parameters in the model. However, if the units in the SCADA system datasource are different, this is where the user can indicate what those units are so that they can be adjusted when being imported.
The user picks the field in the right column and then selects the correct units from the drop down list.
SCADA Signals - Signal Value Mappings
In some cases, the values from the database source must be transformed into values that are expected in the model. Use SCADA Signal Mapping tab to set up these transformations (see SCADA Signal Mapping).
